package org.millioncall.yueyoga.admin.service.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.commons.lang.StringUtils;
import org.millioncall.openplatform.util.DateTimeUtils;
import org.millioncall.yueyoga.admin.dao.StatisticsDAO;
import org.millioncall.yueyoga.admin.service.StatisticsService;
import org.millioncall.yueyoga.admin.service.stat.GroupByTableBuilder;
import org.millioncall.yueyoga.admin.service.stat.TableView;
import org.millioncall.yueyoga.admin.service.stat.Tr;

import com.alibaba.fastjson.JSON;

/**
 * 信息统计
 * 
 * @author lishoujing
 * 
 */
public class StatisticsServiceImpl implements StatisticsService {

	private StatisticsDAO statisticsDAO;

	/**
	 * 简单统计公共方法
	 */
	public StatisticsDAO getStatisticsDAO() {
		return statisticsDAO;
	}

	public void setStatisticsDAO(StatisticsDAO statisticsDAO) {
		this.statisticsDAO = statisticsDAO;
	}

	@Override
	public List<Object[]> commonStatQuery(List<String> leftGroupBy,
			List<String> topGroupBy, String statCol, String function) {

		return null;
	}

	/**
	 * 会员信息简单统计
	 */
	@Override
	public List<Object[]> memberStatQuery(List<String> leftGroupBy,
			List<String> topGroupBy, String statCol, String function) {
		String selectStr = "SELECT ";
		String blankStr = " ";
		String commaStr = ",";
		String groupByStr = " GROUP BY ";
		String joinStr = " "; // --关联表查询
		if (leftGroupBy != null && !leftGroupBy.isEmpty()) {
			for (String str : leftGroupBy) {
				Map<String, String> map = relatedMemberSQL(str);
				String strTemp = str;
				str = map.get("str");
				joinStr += map.get("joinStr");
				// --如果是年龄分组
				if ("birthday".equals(strTemp)) {
					groupByStr += blankStr + " t1.nld " + commaStr;
				} else if ("consumeType".equals(strTemp)) {
					groupByStr += blankStr + " xfje " + commaStr;
				} else {
					groupByStr += blankStr + str + commaStr;
				}
				selectStr += blankStr + str + commaStr;
			}
		}

		if (topGroupBy != null && !topGroupBy.isEmpty()) {
			for (String str : topGroupBy) {
				Map<String, String> map = relatedMemberSQL(str);
				String strTemp = str;
				str = map.get("str");
				joinStr += map.get("joinStr");
				// --如果是年龄分组
				if ("birthday".equals(strTemp)) {
					groupByStr += blankStr + " t1.nld " + commaStr;
				} else if ("consumeType".equals(strTemp)) {
					groupByStr += blankStr + " xfje " + commaStr;
				} else {
					groupByStr += blankStr + str + commaStr;
				}
				selectStr += blankStr + str + commaStr;
			}
		}
		groupByStr = groupByStr.substring(0, groupByStr.length() - 1);
		// added by zangjinyu on 2014-6-30
		if ("GROUP BY".equals(groupByStr.trim())) {// 表示无分组条件
			groupByStr = " ";
		}

		// --统计数量
		if ("id".equals(statCol)) {
			statCol = " m.memberId ";
		}
		// --只有当统计的列为积分或者余额时，才进行统计
		else if ("score".equals(statCol) || "remaining".equals(statCol)) {
			joinStr += " INNER JOIN yueyoga_remaining r ON m.memberId = r.memberId ";
		} else {
			statCol = "m." + statCol;
		}

		selectStr += function + "(" + statCol + ")";

		String tableName = "yueyoga_member m ";

		String sql = selectStr + " from " + tableName + joinStr + groupByStr;

		List<Object[]> results = statisticsDAO.commonStat(sql,
				new HashMap<String, Object>());
		return results;
	}

	/**
	 * 班级信息简单统计
	 */
	@Override
	public List<Object[]> clazzStatQuery(List<String> leftGroupBy,
			List<String> topGroupBy, String statCol, String function) {
		String selectStr = "SELECT ";
		String blankStr = " ";
		String commaStr = ",";
		String groupByStr = " GROUP BY ";
		String joinStr = " "; // --关联表查询
		// --左侧分组
		if (leftGroupBy != null && !leftGroupBy.isEmpty()) {
			for (String str : leftGroupBy) {
				// --班级状态
				if ("state".equals(str)) {
					str = " zt.name ";
					joinStr += " LEFT JOIN yueyoga_status zt ON zt.id = c.state ";
				} else {
					str = " c." + str;
				}
				groupByStr += blankStr + str + commaStr;
				selectStr += blankStr + str + commaStr;
			}
		}
		// --头部分组
		if (topGroupBy != null && !topGroupBy.isEmpty()) {
			for (String str : topGroupBy) {
				// --班级状态
				if ("state".equals(str)) {
					str = " zt.name ";
					joinStr += " LEFT JOIN yueyoga_status zt ON zt.id = c.state ";
				} else {
					str = " c." + str;
				}
				groupByStr += blankStr + str + commaStr;
				selectStr += blankStr + str + commaStr;
			}
		}
		// --统计班级产品
		if ("product".equals(statCol)) {
			statCol = " cp.product ";
			joinStr += " LEFT JOIN yueyoga_class_product cp ON cp.classId = c.id ";
		} else {
			statCol = " c." + statCol;
		}

		selectStr += function + "(" + statCol + ")";
		groupByStr = groupByStr.substring(0, groupByStr.length() - 1);

		String tableName = "yueyoga_class c ";

		String sql = selectStr + " from " + tableName + joinStr + groupByStr;

		List<Object[]> results = statisticsDAO.commonStat(sql,
				new HashMap<String, Object>());
		return results;
	}

	/**
	 * 活动信息简单统计
	 */
	@Override
	public List<Object[]> liftClubStatQuery(List<String> leftGroupBy,
			List<String> topGroupBy, String statCol, String function) {
		String selectStr = "SELECT ";
		String blankStr = " ";
		String commaStr = ",";
		String groupByStr = " GROUP BY ";
		String joinStr = " "; // --关联表查询
		// --左侧分组
		if (leftGroupBy != null && !leftGroupBy.isEmpty()) {
			for (String str : leftGroupBy) {
				// --活动老师
				if ("coachType0Id".equals(str)) {
					str = " ls.name ";
					joinStr += " LEFT JOIN yueyoga_coach ls ON ls.coachId = lc.coachType0Id ";

					// --活动助教
				} else if ("coachType1Id".equals(str)) {
					str = " zj.name ";
					joinStr += " LEFT JOIN yueyoga_coach zj ON zj.coachId = lc.coachType1Id ";

					// --活动场地
				} else if ("placeId".equals(str)) {
					str = " cd.name ";
					joinStr += " LEFT JOIN yueyoga_place cd ON cd.id = lc.placeId ";
				} else {
					str = " lc." + str;
				}
				groupByStr += blankStr + str + commaStr;
				selectStr += blankStr + str + commaStr;
			}
		}
		// --头部分组
		if (topGroupBy != null && !topGroupBy.isEmpty())
			for (String str : topGroupBy) {
				// --活动老师
				if ("coachType0Id".equals(str)) {
					str = " ls.name ";
					joinStr += " LEFT JOIN yueyoga_coach ls ON ls.coachId = lc.coachType0Id ";

					// --活动助教
				} else if ("coachType1Id".equals(str)) {
					str = " zj.name ";
					joinStr += " LEFT JOIN yueyoga_coach zj ON zj.coachId = lc.coachType1Id ";

					// --活动场地
				} else if ("placeId".equals(str)) {
					str = " cd.name ";
					joinStr += " LEFT JOIN yueyoga_place cd ON cd.id = lc.placeId ";
				} else {
					str = " lc." + str;
				}
				groupByStr += blankStr + str + commaStr;
				selectStr += blankStr + str + commaStr;
			}
		statCol = " lc." + statCol;
		selectStr += function + "(" + statCol + ")";
		groupByStr = groupByStr.substring(0, groupByStr.length() - 1);

		String tableName = "yueyoga_lift_club lc ";

		String sql = selectStr + " from " + tableName + joinStr + groupByStr;

		List<Object[]> results = statisticsDAO.commonStat(sql,
				new HashMap<String, Object>());
		return results;
	}

	/**
	 * 任务信息简单统计
	 */
	@Override
	public List<Object[]> taskStatQuery(List<String> leftGroupBy,
			List<String> topGroupBy, String statCol, String function) {
		String selectStr = "SELECT ";
		String blankStr = " ";
		String commaStr = ",";
		String groupByStr = " GROUP BY ";
		String joinStr = " "; // --关联表查询
		// --左侧分组
		if (leftGroupBy != null && !leftGroupBy.isEmpty()) {
			for (String str : leftGroupBy) {
				// --工作安排
				if ("subStatusId".equals(str)) {
					str = " gzap.name ";
					joinStr = "LEFT JOIN yueyoga_status gzap ON gzap.id = t.subStatusId ";
					// --任务状态
				} else if ("state".equals(str)) {
					str = " CASE t.state WHEN '0' THEN '已取消' WHEN '1' THEN '进行中' WHEN '2' THEN '已完成' ELSE t.state END ";
				} else {
					str = " t." + str;
				}
				groupByStr += blankStr + str + commaStr;
				selectStr += blankStr + str + commaStr;
			}
		}
		// --头部分组
		if (topGroupBy != null && !topGroupBy.isEmpty()) {
			for (String str : topGroupBy) {
				// --工作安排
				if ("subStatusId".equals(str)) {
					str = " gzap.name ";
					joinStr = "LEFT JOIN yueyoga_status gzap ON gzap.id = t.subStatusId ";
					// --任务状态
				} else if ("state".equals(str)) {
					str = " CASE t.state WHEN '0' THEN '已取消' WHEN '1' THEN '进行中' WHEN '2' THEN '已完成' ELSE t.state END ";
				} else {
					str = " t." + str;
				}
				groupByStr += blankStr + str + commaStr;
				selectStr += blankStr + str + commaStr;
			}
		}
		statCol = " t." + statCol;
		selectStr += function + "(" + statCol + ")";
		groupByStr = groupByStr.substring(0, groupByStr.length() - 1);

		String tableName = "yueyoga_task t ";

		String sql = selectStr + " from " + tableName + joinStr + groupByStr;

		List<Object[]> results = statisticsDAO.commonStat(sql,
				new HashMap<String, Object>());
		return results;
	}

	/**
	 * 商品信息简单统计
	 */
	@Override
	public List<Object[]> goodsStatQuery(List<String> leftGroupBy,
			List<String> topGroupBy, String statCol, String function) {
		String selectStr = "SELECT ";
		String blankStr = " ";
		String commaStr = ",";
		String groupByStr = " GROUP BY ";
		String joinStr = " "; // --关联表查询
		// --左侧分组
		if (leftGroupBy != null && !leftGroupBy.isEmpty()) {
			for (String str : topGroupBy) {
				// --商品类型
				if ("type".equals(str)) {
					str = " gt.name ";
					joinStr += " LEFT JOIN yueyoga_goodtype gt on gt.goodTypeId = g.type ";
					// --价格区间
				} else if ("price".equals(str)) {
					str = " g1.jgqx ";
					joinStr += " inner join (select '<50' as jgqx,0 as min,50 as max ";
					joinStr += " UNION select '50-100',50,100 UNION select '100-300',100,300 ";
					joinStr += " UNION select '300-500',300,500 UNION select '>500',500,100000 ) g1 on (";
					joinStr += " g.price >= min and g.price <max)";
				} else {
					str = " g." + str;
				}
				groupByStr += blankStr + str + commaStr;
				selectStr += blankStr + str + commaStr;
			}
		}
		// --头部分组
		if (topGroupBy != null && !topGroupBy.isEmpty()) {
			for (String str : topGroupBy) {
				// --商品类型
				if ("type".equals(str)) {
					str = " gt.name ";
					joinStr += " LEFT JOIN yueyoga_goodtype gt on gt.goodTypeId = g.type ";
					// --价格区间
				} else if ("price".equals(str)) {
					str = " g1.jgqx ";
					joinStr += " inner join (select '<50' as jgqx,0 as min,50 as max ";
					joinStr += " UNION select '50-100',50,100 UNION select '100-300',100,300 ";
					joinStr += " UNION select '300-500',300,500 UNION select '>500',500,100000 ) g1 on (";
					joinStr += " g.price >= min and g.price <max)";
				} else {
					str = " g." + str;
				}
				groupByStr += blankStr + str + commaStr;
				selectStr += blankStr + str + commaStr;
			}
		}
		statCol = "g." + statCol;
		selectStr += function + "(" + statCol + ")";
		groupByStr = groupByStr.substring(0, groupByStr.length() - 1);

		String tableName = "yueyoga_goods g ";

		String sql = selectStr + " from " + tableName + joinStr + groupByStr;

		List<Object[]> results = statisticsDAO.commonStat(sql,
				new HashMap<String, Object>());
		return results;
	}

	/**
	 * 产品信息简单统计
	 */
	@Override
	public List<Object[]> productStatQuery(List<String> leftGroupBy,
			List<String> topGroupBy, String statCol, String function) {
		String selectStr = "SELECT ";
		String blankStr = " ";
		String commaStr = ",";
		String groupByStr = " GROUP BY ";
		String joinStr = " "; // --关联表查询
		// --左侧分组
		if (leftGroupBy != null && !leftGroupBy.isEmpty()) {
			for (String str : leftGroupBy) {
				// --产品类型
				if ("type".equals(str)) {
					str = " lx.name ";
					joinStr += " LEFT JOIN yueyoga_status lx on lx.id = p.type ";
					// --价格区间
				} else if ("price".equals(str)) {
					str = " p1.jgqx ";
					joinStr += " inner join (select '<500' as jgqx,0 as min,500 as max ";
					joinStr += " UNION select '500-1000',500,1000 UNION select '1000-3000',1000,3000 ";
					joinStr += " UNION select '3000-5000',3000,5000 UNION select '>5000',5000,1000000 ) p1 on (";
					joinStr += " p.price >= min and p.price <max)";
				} else {
					str = " p." + str;
				}
				groupByStr += blankStr + str + commaStr;
				selectStr += blankStr + str + commaStr;
			}
		}
		// --头部分组
		if (topGroupBy != null && !topGroupBy.isEmpty())
			for (String str : topGroupBy) {
				// --产品类型
				if ("type".equals(str)) {
					str = " lx.name ";
					joinStr += " LEFT JOIN yueyoga_status lx on lx.id = p.type ";
					// --价格区间
				} else if ("price".equals(str)) {
					str = " p1.jgqx ";
					joinStr += " inner join (select '<500' as jgqx,0 as min,500 as max ";
					joinStr += " UNION select '500-1000',500,1000 UNION select '1000-3000',1000,3000 ";
					joinStr += " UNION select '3000-5000',3000,5000 UNION select '>5000',5000,1000000 ) p1 on (";
					joinStr += " p.price >= min and p.price <max)";
				} else {
					str = " p." + str;
				}
				groupByStr += blankStr + str + commaStr;
				selectStr += blankStr + str + commaStr;
			}
		if ("id".equals(statCol)) {
			statCol = " p.productId ";
		} else {
			statCol = "p." + statCol;
		}
		selectStr += function + "(" + statCol + ")";
		groupByStr = groupByStr.substring(0, groupByStr.length() - 1);

		String tableName = "yueyoga_product p ";

		String sql = selectStr + " from " + tableName + joinStr + groupByStr;

		List<Object[]> results = statisticsDAO.commonStat(sql,
				new HashMap<String, Object>());
		return results;
	}

	// --会员条件语句拼接
	private Map<String, String> relatedMemberSQL(String str) {
		Map<String, String> map = new HashMap<String, String>();
		String joinStr = "";

		// --消费类型
		if ("consumeType".equals(str)) {
			str = " xflx ";
			joinStr += "INNER JOIN (select mo.memberId,ord.money as xfje,ord.type as xflx from yueyoga_order ord "
					+ " INNER JOIN yueyoga_member mo on mo.memberId = ord.memberId "
					+ " UNION select mrc.memberId,rge.recharge,rge.logType from yueyoga_recharge rge "
					+ " INNER JOIN yueyoga_member mrc on rge.memberId = mrc.memberId) cme ";
		}
		// --信息来源
		else if ("marketingSource".equals(str)) {
			str = " xxly.name ";
			joinStr = "left join yueyoga_status xxly on xxly.id = m.marketingSource ";

			// --状态
		} else if ("status".equals(str)) {
			str = " zt.name ";
			joinStr = "left join yueyoga_status zt on zt.code = (select zzt.parentCode from yueyoga_status zzt where zzt.code = m.subStatus)";
			// --子状态
		} else if ("subStatus".equals(str)) {
			str = " zzt.name ";
			joinStr = "left join yueyoga_status zzt on zzt.code = m.subStatus ";
			// --渠道
		} else if ("marketingChannel".equals(str)) {
			str = " qd.name ";
			joinStr = "left join yueyoga_status qd on qd.code =(select zqd.parentCode from yueyoga_status zqd where zqd.id = m.subMarketingChannel) ";

			// --子渠道
		} else if ("subMarketingChannel".equals(str)) {
			str = " zqd.name ";
			joinStr = "left join yueyoga_status zqd on zqd.id = m.subMarketingChannel ";

			// --省份
		} else if ("provinceId".equals(str)) {
			str = " prve.name ";
			joinStr = "left join yueyoga_area prve on prve.areaId = m.provinceId ";

			// --市区
		} else if ("cityId".equals(str)) {
			str = " city.name ";
			joinStr = "left join yueyoga_area city on city.areaId = m.cityId ";

			// --小区
		} else if ("areaId".equals(str)) {
			str = " area.name ";
			joinStr = "left join yueyoga_area area on area.areaId = m.areaId ";

			// --地域
		} else if ("domainId".equals(str)) {
			str = " dq.name ";
			joinStr = "left join yueyoga_area dq on dq.areaId = m.domainId ";

			// --年龄
		} else if ("birthday".equals(str)) {
			Date date = new Date();
			String dt = DateTimeUtils.getInstance().format(date, "yyyy-MM-dd");
			// --计算年龄
			str = " t1.nld ";
			joinStr += " INNER JOIN (select '<=20' as nld,0 as min,20 as max ";
			joinStr += " UNION select '21-30',20,30 UNION select '31-40',30,40 ";
			joinStr += " UNION select '41-50',40,50 UNION select '>50',50,200) t1 on (";
			joinStr += " ((date_format('" + dt
					+ "', '%Y') - date_format(m.birthday, '%Y')) >= min ";
			joinStr += " and (date_format('" + dt
					+ "', '%Y') - date_format(m.birthday, '%Y')) < max )) ";

			// --按班级
		} else if ("clazz".equals(str)) {
			str = " c.name ";
			joinStr += " LEFT JOIN yueyoga_class_member cm ON cm.memberId = m.memberId";
			joinStr += " LEFT JOIN yueyoga_class c ON cm.classId = c.id ";
			// --按活动
		} else if ("liftClub".equals(str)) {
			str = " lc.name ";
			joinStr += " LEFT JOIN yueyoga_member_club mc on mc.memberId = m.memberId INNER JOIN yueyoga_lift_club lc ON mc.club = lc.id ";
		} else {
			str = "m." + str;
		}
		map.put("str", str);
		map.put("joinStr", joinStr);
		return map;
	}

	// -----------------------------------------------------------------------------------------
	/**
	 * -------------会员带条件查询----------------
	 */
	@Override
	public List<Object[]> memberStatQueryByCdts(List<String> leftGroupBy,
			List<String> topGroupBy, String statCol, String function, String criteria) {
		String selectStr = "SELECT ";
		String blankStr = " ";
		String commaStr = ",";
		// --查询条件
		String criteriaStr = "";
		String groupByStr = " GROUP BY ";
		String joinStr = " "; // --关联表查询

		// --左侧
		if (leftGroupBy != null && !leftGroupBy.isEmpty()) {
			for (String str : leftGroupBy) {
				Map<String, String> map = relatedMemberSQL(str);
				String strTemp = str;
				str = map.get("str");
				joinStr += map.get("joinStr");
				// --如果是年龄分组
				if ("birthday".equals(strTemp)) {
					groupByStr += blankStr + " t1.nld " + commaStr;
				} else if ("consumeType".equals(strTemp)) {
					groupByStr += blankStr + " xflx " + commaStr;
				} else {
					groupByStr += blankStr + str + commaStr;
				}
				selectStr += blankStr + str + commaStr;
			}
		}

		// --头部
		if (topGroupBy != null && !topGroupBy.isEmpty()) {
			for (String str : topGroupBy) {
				Map<String, String> map = relatedMemberSQL(str);
				String strTemp = str;
				str = map.get("str");
				joinStr += map.get("joinStr");
				// --如果是年龄分组
				if ("birthday".equals(strTemp)) {
					groupByStr += blankStr + " t1.nld " + commaStr;
				} else if ("consumeType".equals(strTemp)) {
					groupByStr += blankStr + " xflx " + commaStr;
				} else {
					groupByStr += blankStr + str + commaStr;
				}
				selectStr += blankStr + str + commaStr;
			}
		}

		// --分组
		groupByStr = groupByStr.substring(0, groupByStr.length() - 1);
		// added by zangjinyu on 2014-6-30
		if ("GROUP BY".equals(groupByStr.trim())) {// 表示无分组条件
			groupByStr = " ";
		}

		// --统计数量
		if ("id".equals(statCol)) {
			statCol = " m.memberId ";
		}
		// --只有当统计的列为积分或者余额时，才进行统计
		else if ("score".equals(statCol) || "remaining".equals(statCol)) {
			joinStr += " INNER JOIN yueyoga_remaining r ON m.memberId = r.memberId ";
		} else {
			statCol = "m." + statCol;
		}

		selectStr += function + "(" + statCol + ")";

		// --查询条件
		if (StringUtils.isNotBlank(criteria)) {
			criteriaStr += " where ";
			if (criteria.contains("hyzt.name")) {
				joinStr += " LEFT JOIN yueyoga_status hyzt on hyzt.code = (select hzzt.parentCode from yueyoga_status hzzt where hzzt.code = m.subStatus)";
			}
			if (criteria.contains("hyzzt.name")) {
				joinStr += " LEFT JOIN yueyoga_status hyzzt on hyzzt.code = m.subStatus ";
			}
			if (criteria.contains("scqd.name")) {
				joinStr += " LEFT JOIN yueyoga_status scqd on scqd.code = (select hzqd.parentCode from yueyoga_status hzqd where hzqd.code = m.subMarketingChannel) ";
			}
			if (criteria.contains("sczqd.name")) {
				joinStr += " LEFT JOIN yueyoga_status sczqd on sczqd.code = m.subMarketingChannel ";
			}
			if (criteria.contains("xxly.name")) {
				joinStr += " LEFT JOIN yueyoga_status xxly on xxly.id = m.marketingSource ";
			}
			if (criteria.contains("hjgw.name")) {
				joinStr += " LEFT JOIN yueyoga_userdetail hjgw on hjgw.id = m.keeperId ";
			}
			if (criteria.contains("prov.name")) {
				joinStr += " LEFT JOIN yueyoga_area prov on prov.areaId = m.provinceId ";
			}
			if (criteria.contains("city.name")) {
				joinStr += " LEFT JOIN yueyoga_area city on city.areaId = m.cityId ";
			}
			if (criteria.contains("area.name")) {
				joinStr += " LEFT JOIN yueyoga_area area on area.areaId = m.areaId ";
			}
			if (criteria.contains("domain.name")) {
				joinStr += " LEFT JOIN yueyoga_area domain on domain.areaId = m.domainId ";
			}
			if (criteria.contains("hall.name")) {
				joinStr += " LEFT JOIN yueyoga_hall hall on hall.code = m.organizationId ";
			}
			if (criteria.contains("respor.name")) {
				joinStr += " LEFT JOIN yueyoga_userdetail respor on respor.id = m.userId ";
			}
			if (criteria.contains("m.age")) {
				String date = DateTimeUtils.getInstance()
						.format(new Date(), "yyyy-MM-dd");
				criteria = criteria.replace("m.age", "(date_format('" + date
						+ "', '%Y') - date_format(m.birthday, '%Y'))");
			}
			criteriaStr += criteria;
		}

		String tableName = "yueyoga_member m ";

		String sql = selectStr + " from " + tableName + joinStr + criteriaStr
				+ groupByStr;

		List<Object[]> results = statisticsDAO.commonStat(sql,
				new HashMap<String, Object>());
		return results;
	}

	/***
	 * -------------班级带条件查询----------------
	 */
	@Override
	public List<Object[]> clazzStatQueryByCdts(List<String> leftGroupBy,
			List<String> topGroupBy, String statCol, String function, String criteria) {
		String selectStr = "SELECT ";
		String blankStr = " ";
		String commaStr = ",";
		String groupByStr = " GROUP BY ";
		String criteriaStr = "";
		String joinStr = " "; // --关联表查询
		// --左侧分组
		if (leftGroupBy != null && !leftGroupBy.isEmpty()) {
			for (String str : leftGroupBy) {
				// --班级状态
				if ("state".equals(str)) {
					str = " zt.name ";
					joinStr += " LEFT JOIN yueyoga_status zt ON zt.id = cls.state ";
				} else {
					str = " cls." + str;
				}
				groupByStr += blankStr + str + commaStr;
				selectStr += blankStr + str + commaStr;
			}
		}
		// --头部分组
		if (topGroupBy != null && !topGroupBy.isEmpty()) {
			for (String str : topGroupBy) {
				// --班级状态
				if ("state".equals(str)) {
					str = " zt.name ";
					joinStr += " LEFT JOIN yueyoga_status zt ON zt.id = cls.state ";
				} else {
					str = " cls." + str;
				}
				groupByStr += blankStr + str + commaStr;
				selectStr += blankStr + str + commaStr;
			}
		}
		// --统计班级产品
		if ("product".equals(statCol)) {
			statCol = " cp.product ";
			joinStr += " LEFT JOIN yueyoga_class_product cp ON cp.classId = cls.id ";
		} else {
			statCol = " cls." + statCol;
		}

		selectStr += function + "(" + statCol + ")";
		groupByStr = groupByStr.substring(0, groupByStr.length() - 1);

		// --查询条件
		if (StringUtils.isNotBlank(criteria)) {
			if (criteria.contains("bjzt.name")) {
				joinStr += " LEFT JOIN yueyoga_status bjzt ON bjzt.id = cls.state ";
			}
			if (criteria.contains("bjfzr.name")) {
				joinStr += " LEFT JOIN yueyoga_userdetail bjfzr ON bjfzr.id = cls.assignedToId ";
			}
			criteriaStr = " where " + criteria;
		}
		String tableName = "yueyoga_class cls ";

		String sql = selectStr + " from " + tableName + joinStr + criteriaStr
				+ groupByStr;

		List<Object[]> results = statisticsDAO.commonStat(sql,
				new HashMap<String, Object>());
		return results;
	}

	/**
	 * -------------活动带条件查询----------------
	 */
	@Override
	public List<Object[]> liftClubStatQueryByCdts(List<String> leftGroupBy,
			List<String> topGroupBy, String statCol, String function, String criteria) {
		String selectStr = "SELECT ";
		String blankStr = " ";
		String commaStr = ",";
		String groupByStr = " GROUP BY ";
		String criteriaStr = "";
		String joinStr = " "; // --关联表查询
		// --左侧分组
		if (leftGroupBy != null && !leftGroupBy.isEmpty()) {
			for (String str : leftGroupBy) {
				// --活动老师
				if ("coachType0Id".equals(str)) {
					str = " ls.name ";
					joinStr += " LEFT JOIN yueyoga_coach ls ON ls.coachId = lc.coachType0Id ";

					// --活动助教
				} else if ("coachType1Id".equals(str)) {
					str = " zj.name ";
					joinStr += " LEFT JOIN yueyoga_coach zj ON zj.coachId = lc.coachType1Id ";

					// --活动场地
				} else if ("placeId".equals(str)) {
					str = " cd.name ";
					joinStr += " LEFT JOIN yueyoga_place cd ON cd.id = lc.placeId ";
				} else {
					str = " lc." + str;
				}
				groupByStr += blankStr + str + commaStr;
				selectStr += blankStr + str + commaStr;
			}
		}
		// --头部分组
		if (topGroupBy != null && !topGroupBy.isEmpty())
			for (String str : topGroupBy) {
				// --活动老师
				if ("coachType0Id".equals(str)) {
					str = " ls.name ";
					joinStr += " LEFT JOIN yueyoga_coach ls ON ls.coachId = lc.coachType0Id ";

					// --活动助教
				} else if ("coachType1Id".equals(str)) {
					str = " zj.name ";
					joinStr += " LEFT JOIN yueyoga_coach zj ON zj.coachId = lc.coachType1Id ";

					// --活动场地
				} else if ("placeId".equals(str)) {
					str = " cd.name ";
					joinStr += " LEFT JOIN yueyoga_place cd ON cd.id = lc.placeId ";
				} else {
					str = " lc." + str;
				}
				groupByStr += blankStr + str + commaStr;
				selectStr += blankStr + str + commaStr;
			}
		statCol = " lc." + statCol;
		selectStr += function + "(" + statCol + ")";
		groupByStr = groupByStr.substring(0, groupByStr.length() - 1);

		// --查询条件
		if (StringUtils.isNotBlank(criteria)) {
			if (criteria.contains("hdcd.name")) {
				joinStr += " LEFT JOIN yueyoga_place hdcd ON hdcd.id = lc.placeId ";
			}
			if (criteria.contains("kcls.name")) {
				joinStr += " LEFT JOIN yueyoga_coach kcls ON kcls.coachId = lc.coachType0Id ";
			}
			if (criteria.contains("kczj.name")) {
				joinStr += " LEFT JOIN yueyoga_coach kczj ON kczj.coachId = lc.coachType1Id ";
			}
			if (criteria.contains("kcfzr.name")) {
				joinStr += " LEFT JOIN yueyoga_userdetail kcfzr ON kcfzr.id = lc.responsibleId ";
			}
			criteriaStr = " where " + criteria;
		}

		String tableName = "yueyoga_lift_club lc ";

		String sql = selectStr + " from " + tableName + joinStr + criteriaStr
				+ groupByStr;

		List<Object[]> results = statisticsDAO.commonStat(sql,
				new HashMap<String, Object>());
		return results;
	}

	/**
	 * -------------任务带条件查询----------------
	 */
	@Override
	public List<Object[]> taskStatQueryByCdts(List<String> leftGroupBy,
			List<String> topGroupBy, String statCol, String function, String criteria) {
		String selectStr = "SELECT ";
		String blankStr = " ";
		String commaStr = ",";
		String groupByStr = " GROUP BY ";
		String criteriaStr = "";
		String joinStr = " "; // --关联表查询
		// --左侧分组
		if (leftGroupBy != null && !leftGroupBy.isEmpty()) {
			for (String str : leftGroupBy) {
				// --工作安排
				if ("subStatusId".equals(str)) {
					str = " rwgzap.name ";
					joinStr = "LEFT JOIN yueyoga_status rwgzap ON rwgzap.id = t.subStatusId ";
					// --任务状态
				} else if ("state".equals(str)) {
					str = " CASE t.state WHEN '0' THEN '已取消' WHEN '1' THEN '进行中' WHEN '2' THEN '已完成' ELSE t.state END ";
				} else {
					str = " t." + str;
				}
				groupByStr += blankStr + str + commaStr;
				selectStr += blankStr + str + commaStr;
			}
		}
		// --头部分组
		if (topGroupBy != null && !topGroupBy.isEmpty()) {
			for (String str : topGroupBy) {
				// --工作安排
				if ("subStatusId".equals(str)) {
					str = " rwgzap.name ";
					joinStr = "LEFT JOIN yueyoga_status rwgzap ON rwgzap.id = t.subStatusId ";
					// --任务状态
				} else if ("state".equals(str)) {
					str = " CASE t.state WHEN '0' THEN '已取消' WHEN '1' THEN '进行中' WHEN '2' THEN '已完成' ELSE t.state END ";
				} else {
					str = " t." + str;
				}
				groupByStr += blankStr + str + commaStr;
				selectStr += blankStr + str + commaStr;
			}
		}
		statCol = " t." + statCol;
		selectStr += function + "(" + statCol + ")";
		groupByStr = groupByStr.substring(0, groupByStr.length() - 1);
		// --查询条件
		if (StringUtils.isNotBlank(criteria)) {
			criteriaStr = " where " + criteria;
			// --工作安排
			if (criteria.contains("gzap.name")) {
				joinStr += " LEFT JOIN yueyoga_status gzap ON gzap.id = t.subStatusId ";
				// --任务会员
			}
			if (criteria.contains("rwhy.name")) {
				joinStr += " LEFT JOIN yueyoga_member rwhy ON rwhy.memberId = t.memberId ";
				// --任务创建人
			}
			if (criteria.contains("rwcjr.name")) {
				joinStr += " LEFT JOIN yueyoga_userdetail rwcjr ON rwcjr.id = t.userId ";
				// --任务负责人
			}
			if (criteria.contains("rwfzr.name")) {
				joinStr += " LEFT JOIN yueyoga_userdetail rwfzr ON rwfzr.id = t.resporId ";
			}
			if (criteria.contains("rwzt.name")) {
				joinStr += " LEFT JOIN(select '已取消' as name ,0 as ztxh UNION select '进行中' ,1 ";
				joinStr += " UNION select '已完成' ,2 ) rwzt ON t.state = ztxh";
			}
		}
		String tableName = "yueyoga_task t ";

		String sql = selectStr + " from " + tableName + joinStr + criteriaStr
				+ groupByStr;

		List<Object[]> results = statisticsDAO.commonStat(sql,
				new HashMap<String, Object>());
		return results;
	}

	/**
	 * -------------商品带条件查询----------------
	 */
	@Override
	public List<Object[]> goodsStatQueryByCdts(List<String> leftGroupBy,
			List<String> topGroupBy, String statCol, String function, String criteria) {
		String selectStr = "SELECT ";
		String blankStr = " ";
		String commaStr = ",";
		String groupByStr = " GROUP BY ";
		String criteriaStr = "";
		String joinStr = " "; // --关联表查询
		// --左侧分组
		if (leftGroupBy != null && !leftGroupBy.isEmpty()) {
			for (String str : leftGroupBy) {
				// --商品类型
				if ("type".equals(str)) {
					str = " gt.typeName ";
					joinStr += " LEFT JOIN yueyoga_goodtype gt on gt.goodTypeId = g.type ";
					// --价格区间
				} else if ("price".equals(str)) {
					str = " g1.jgqx ";
					joinStr += " inner join (select '<50' as jgqx,0 as min,50 as max ";
					joinStr += " UNION select '50-100',50,100 UNION select '100-300',100,300 ";
					joinStr += " UNION select '300-500',300,500 UNION select '>500',500,100000 ) g1 on (";
					joinStr += " g.price >= min and g.price <max)";
				} else {
					str = " g." + str;
				}
				groupByStr += blankStr + str + commaStr;
				selectStr += blankStr + str + commaStr;
			}
		}
		// --头部分组
		if (topGroupBy != null && !topGroupBy.isEmpty()) {
			for (String str : topGroupBy) {
				// --商品类型
				if ("type".equals(str)) {
					str = " gt.typeName ";
					joinStr += " LEFT JOIN yueyoga_goodtype gt on gt.goodTypeId = g.type ";
					// --价格区间
				} else if ("price".equals(str)) {
					str = " g1.jgqx ";
					joinStr += " inner join (select '<50' as jgqx,0 as min,50 as max ";
					joinStr += " UNION select '50-100',50,100 UNION select '100-300',100,300 ";
					joinStr += " UNION select '300-500',300,500 UNION select '>500',500,100000 ) g1 on (";
					joinStr += " g.price >= min and g.price <max)";
				} else {
					str = " g." + str;
				}
				groupByStr += blankStr + str + commaStr;
				selectStr += blankStr + str + commaStr;
			}
		}
		statCol = "g." + statCol;
		selectStr += function + "(" + statCol + ")";
		groupByStr = groupByStr.substring(0, groupByStr.length() - 1);
		// --查询条件
		if (StringUtils.isNotBlank(criteria)) {
			if (criteria.contains("splx.typeName")) {
				joinStr += " LEFT JOIN yueyoga_goodtype splx ON splx.goodTypeId = g.type ";
			}
			criteriaStr = " where " + criteria;
		}
		String tableName = "yueyoga_goods g ";

		String sql = selectStr + " from " + tableName + joinStr + criteriaStr
				+ groupByStr;

		List<Object[]> results = statisticsDAO.commonStat(sql,
				new HashMap<String, Object>());
		return results;
	}

	/**
	 * -------------产品带条件查询----------------
	 */
	@Override
	public List<Object[]> productStatQueryByCdts(List<String> leftGroupBy,
			List<String> topGroupBy, String statCol, String function, String criteria) {
		String selectStr = "SELECT ";
		String blankStr = " ";
		String commaStr = ",";
		String groupByStr = " GROUP BY ";
		String criteriaStr = "";
		String joinStr = " "; // --关联表查询
		// --左侧分组
		if (leftGroupBy != null && !leftGroupBy.isEmpty()) {
			for (String str : leftGroupBy) {
				// --产品类型
				if ("type".equals(str)) {
					str = " lx.name ";
					joinStr += " LEFT JOIN yueyoga_status lx on lx.id = p.type ";
					// --价格区间
				} else if ("price".equals(str)) {
					str = " p1.jgqx ";
					joinStr += " inner join (select '<500' as jgqx,0 as min,500 as max ";
					joinStr += " UNION select '500-1000',500,1000 UNION select '1000-3000',1000,3000 ";
					joinStr += " UNION select '3000-5000',3000,5000 UNION select '>5000',5000,1000000 ) p1 on (";
					joinStr += " p.price >= min and p.price <max)";
				} else {
					str = " p." + str;
				}
				groupByStr += blankStr + str + commaStr;
				selectStr += blankStr + str + commaStr;
			}
		}
		// --头部分组
		if (topGroupBy != null && !topGroupBy.isEmpty())
			for (String str : topGroupBy) {
				// --产品类型
				if ("type".equals(str)) {
					str = " lx.name ";
					joinStr += " LEFT JOIN yueyoga_status lx on lx.id = p.type ";
					// --价格区间
				} else if ("price".equals(str)) {
					str = " p1.jgqx ";
					joinStr += " inner join (select '<500' as jgqx,0 as min,500 as max ";
					joinStr += " UNION select '500-1000',500,1000 UNION select '1000-3000',1000,3000 ";
					joinStr += " UNION select '3000-5000',3000,5000 UNION select '>5000',5000,1000000 ) p1 on (";
					joinStr += " p.price >= min and p.price <max)";
				} else {
					str = " p." + str;
				}
				groupByStr += blankStr + str + commaStr;
				selectStr += blankStr + str + commaStr;
			}
		if ("id".equals(statCol)) {
			statCol = " p.productId ";
		} else {
			statCol = "p." + statCol;
		}
		selectStr += function + "(" + statCol + ")";
		groupByStr = groupByStr.substring(0, groupByStr.length() - 1);

		// --查询条件
		if (StringUtils.isNotBlank(criteria)) {
			if (criteria.contains("cplx.name")) {
				joinStr += " LEFT JOIN yueyoga_status cplx ON cplx.id = p.type ";
			}
			criteriaStr = " where " + criteria;
		}

		String tableName = "yueyoga_product p ";

		String sql = selectStr + " from " + tableName + joinStr + criteriaStr
				+ groupByStr;

		List<Object[]> results = statisticsDAO.commonStat(sql,
				new HashMap<String, Object>());
		return results;
	}

	@Override
	public String statKeeperRecordQuery(String hallCode, String fromDate, String toDate,
			String keeperId) {
		String selectStr = "SELECT ";
		String selQdStr = "";
		String selZtStr = "";
		String criteriaStr = "";
		String joinQdStr = " "; // --渠道关联表查询
		String joinZtStr = " "; // --状态关联表查询
		// --渠道分组
		String tpQdGroups = " GROUP BY CONCAT(kpr.name,'(',kp.username,')'),scqd.name,sczqd.name ";
		joinQdStr = " LEFT JOIN yueyoga_userdetail kpr on kpr.id = m.keeperId "
				+ " LEFT JOIN security_user_group ugp on ugp.uid = kpr.id left join security_group gp on gp.id = ugp.gid and gp.name = 'keepers' "
				+ " LEFT JOIN yueyoga_user kp on kp.id = m.keeperId  and kp.organizationid='"
				+ hallCode
				+ "' "
				+ " LEFT JOIN yueyoga_status scqd on scqd.code = (select szzt.parentCode from yueyoga_status szzt where szzt.code = m.subMarketingChannel) "
				+ " LEFT JOIN yueyoga_status sczqd on sczqd.code = m.subMarketingChannel ";
		// --状态分组
		String tpZtGroups = " GROUP BY CONCAT(kpr.name,'(',kp.username,')'),hyzt.name,hyzzt.name ";

		joinZtStr = " LEFT JOIN yueyoga_userdetail kpr on kpr.id = m.keeperId "
				+ " LEFT JOIN security_user_group ugp on ugp.uid = kpr.id left join security_group gp on gp.id = ugp.gid and gp.name = 'keepers' "
				+ " LEFT JOIN yueyoga_user kp on kp.id = m.keeperId  and kp.organizationid='"
				+ hallCode
				+ "' "
				+ " LEFT JOIN yueyoga_status hyzt on hyzt.code = (select hzzt.parentCode from yueyoga_status hzzt where hzzt.code = m.subStatus) "
				+ " LEFT JOIN yueyoga_status hyzzt on hyzzt.code = m.subStatus ";

		selQdStr = selectStr
				+ " CONCAT(kpr.name,'(',kp.username,')'),scqd.name,sczqd.name,COUNT(m.memberId) ";
		selZtStr = selectStr
				+ " CONCAT(kpr.name,'(',kp.username,')'),hyzt.name,hyzzt.name,COUNT(m.memberId) ";

		criteriaStr += " where m.organizationId='" + hallCode + "'";
		// --查询条件
		if (StringUtils.isNotBlank(fromDate)) {
			criteriaStr += " and m.inputTime >='" + fromDate + "'";
		}
		if (StringUtils.isNotBlank(toDate)) {
			criteriaStr += " and m.inputTime <='" + toDate + "'";
		}
		if (StringUtils.isNotBlank(keeperId)) {
			criteriaStr += " and kp.id= '" + keeperId + "'";
		}
		String tableName = "yueyoga_member m ";
		String qdSql = selQdStr + " from " + tableName + joinQdStr + criteriaStr
				+ tpQdGroups;
		String ztSql = selZtStr + " from " + tableName + joinZtStr + criteriaStr
				+ tpZtGroups;

		List<Object[]> qdList = statisticsDAO.commonStat(qdSql,
				new HashMap<String, Object>());
		List<Object[]> ztList = statisticsDAO.commonStat(ztSql,
				new HashMap<String, Object>());
		if (qdList == null || qdList.isEmpty() || ztList == null || ztList.isEmpty()) {
			return "";
		}
		GroupByTableBuilder qdGbc = new GroupByTableBuilder(qdList, 1, 2);
		GroupByTableBuilder ztGbc = new GroupByTableBuilder(ztList, 1, 2);
		TableView qdTv = qdGbc.buildTable();
		TableView ztTv = ztGbc.buildTable();
		Map<Integer, Tr> mhtr = new HashMap<Integer, Tr>();
		Map<Integer, Tr> mbtr = new HashMap<Integer, Tr>();

		// --头部循环
		for (int i = 0; i < qdTv.getHeader().size(); i++) {
			Tr tr = qdTv.getHeader().get(i);
			mhtr.put(i, tr);
		}
		// --body体循环
		for (int j = 0; j < qdTv.getBody().size(); j++) {
			Tr tr = qdTv.getBody().get(j);
			mbtr.put(j, tr);
		}

		for (int k = 0; k < ztTv.getHeader().size(); k++) {
			Tr tr = ztTv.getHeader().get(k);
			if (mhtr.containsKey(k)) {
				Tr trt = mhtr.get(k);
				if (tr.getTds() != null && !tr.getTds().isEmpty() && k == 0) {
					tr.getTds().remove(0);
				}
				trt.getTds().addAll(tr.getTds());
			} else {
				mhtr.put(k, tr);
			}
		}

		for (int m = 0; m < ztTv.getBody().size(); m++) {
			Tr tr = ztTv.getBody().get(m);
			if (mbtr.containsKey(m)) {
				Tr trt = mbtr.get(m);
				if (tr.getTds() != null && !tr.getTds().isEmpty()) {
					tr.getTds().remove(0);
				}
				trt.getTds().addAll(tr.getTds());
			} else {
				mbtr.put(m, tr);
			}
		}

		List<Tr> hTrs = new ArrayList<Tr>();
		List<Tr> bTrs = new ArrayList<Tr>();
		Iterator<Entry<Integer, Tr>> ith = mhtr.entrySet().iterator();
		Iterator<Entry<Integer, Tr>> itb = mbtr.entrySet().iterator();
		while (ith.hasNext()) {
			Entry<Integer, Tr> entry = ith.next();
			hTrs.add(entry.getValue());
		}
		while (itb.hasNext()) {
			Entry<Integer, Tr> entry = itb.next();
			bTrs.add(entry.getValue());
		}
		TableView tv = new TableView();
		tv.setHeader(hTrs);
		tv.setBody(bTrs);
		return JSON.toJSONString(tv);
	}
}
